﻿CREATE PROCEDURE [dbo].[CreateStock] 
	@StockTypeId INT,
	@Code VARCHAR(50),
	@Description VARCHAR(255),
	@Weight INT,
	@SizeX INT,
	@SizeY INT,
	@Facing CHAR(1),
	@Colour VARCHAR(50)
AS

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

DECLARE @NextStockId INT
SELECT @NextStockId = MAX(StockId) + 1 FROM [dbo].[Stock]

INSERT INTO
[dbo].[Stock] (StockId, Code, [Description], [Weight], SizeX, SizeY, Facing, StockTypeID, DateCreated, Colour)
VALUES (@NextStockId, @Code, @Description, @Weight, @SizeX, @SizeY, @Facing, @StockTypeId, GETDATE(), @Colour)

SELECT Stock.StockId, Code, [Description], [Weight], SizeX, SizeY, Facing, Colour, Stock.StockTypeId, StockType
FROM dbo.Stock
INNER JOIN dbo.StockType ON StockType.StockTypeID = Stock.StockTypeID
WHERE StockID = @NextStockId